import models
import schemas
from database import get_db, engine
from fastapi import FastAPI, Depends, Query
from sqlalchemy.orm import Session
from sqlalchemy import desc, func
from typing import Optional
from datetime import datetime

# Create tables
models.Base.metadata.create_all(bind=engine)

app = FastAPI()


@app.get("/api/search-records", response_model=schemas.PaginatedResponse)
def get_search_records(
        db: Session = Depends(get_db),
        page: int = Query(1, ge=1, description="Page number"),
        page_size: int = Query(10, ge=1, le=100, description="Items per page"),
        username: Optional[str] = Query(None, description="Filter by username"),
        start_date: Optional[datetime] = Query(None, description="Filter by start date"),
        end_date: Optional[datetime] = Query(None, description="Filter by end date"),
        latest_only: bool = Query(True, description="Only show latest record per user")
):
    if latest_only:
        # 默认页面，查询每个用户最新一条查询记录并展示到界面
        # 使用子查询获取每个用户在给定时间范围内最新的记录
        query = db.query(
            models.SearchInputRecord.username,
            func.max(models.SearchInputRecord.create_time).label('max_time')
        )

        if start_date:
            query = query.filter(models.SearchInputRecord.create_time >= start_date)
        if end_date:
            query = query.filter(models.SearchInputRecord.create_time <= end_date)

        latest_records = query.group_by(models.SearchInputRecord.username).subquery()

        # 主查询与子查询关联
        query = db.query(models.SearchInputRecord).join(
            latest_records,
            (models.SearchInputRecord.username == latest_records.c.username) &
            (models.SearchInputRecord.create_time == latest_records.c.max_time)
        )
        # 等价SQL
        # SELECT *
        # FROM search_input_record AS s
        # JOIN (
        #   SELECT username, MAX(create_time) AS max_time
        #   FROM search_input_record
        #   WHERE create_time BETWEEN [start] AND [end]
        #   GROUP BY username
        # ) AS latest_records
        # ON s.username = latest_records.username
        #   AND s.create_time = latest_records.max_time

    else:
        # 普通查询
        query = db.query(models.SearchInputRecord)

        if username:
            query = query.filter(models.SearchInputRecord.username == username)
        if start_date:
            query = query.filter(models.SearchInputRecord.create_time >= start_date)
        if end_date:
            query = query.filter(models.SearchInputRecord.create_time <= end_date)

    # 获取总数
    total = query.count()

    # 应用排序和分页
    items = query.order_by(desc(models.SearchInputRecord.create_time)) \
        .offset((page - 1) * page_size) \
        .limit(page_size) \
        .all()

    return {
        "total": total,
        "items": items,
        "page": page,
        "page_size": page_size
    }


if __name__ == "__main__":
    import uvicorn

    uvicorn.run(app, host="0.0.0.0", port=8000)
